﻿Imports Microsoft.Office.Interop
Imports MySql.Data.MySqlClient

Public Class frmadd_gate
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim respone As Object
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim idcontainer As String
    Dim idbooking As String
    Dim ctnstat As String

    Dim textDate As String
    Dim stringDate() As String
    Dim stringhour() As String
    Dim sumtxt As String
    Dim sumint As Double
    Dim agentline As String

    Private Sub frmadd_gate_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        txt_voyage.Text = Format(frmsearch_voyage1.idvoyage, "000")
        TextBox2.Text = Format(frmsearch_voyage1.idvoyage, "000")
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture


        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select * from booking where BVOYAGE ='" & frmsearch_voyage1.idvoyage & "' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView2.Items.Clear()
            While (mySqlReader.Read())

                With ListView2.Items.Add(mySqlReader("BNO"))


                    If mySqlReader("BSHIPNAME") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("BSHIPNAME"))
                    End If

                    If mySqlReader("BFORWARDERNAME") Is DBNull.Value Then
                        .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                    Else

                        .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                    End If


                    .SubItems.Add(mySqlReader("BCTNNO") + " x " + mySqlReader("BCTNTYPE"))
                    .SubItems.Add(mySqlReader("BOOKINGID"))
                    .SubItems.Add(mySqlReader("BNICKAGENT"))
                End With



            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select * from ctnmain where CTNSTAT = '1' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())

                With ListView1.Items.Add(mySqlReader("CTNSTRING"))


                    If mySqlReader("CTNAGENT") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("CTNAGENT"))
                    End If
                    If mySqlReader("CTNSIZE") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("CTNSIZE"))
                    End If


                    .SubItems.Add("N" + Format(mySqlReader("CTNVOYN"), "000"))

                    .SubItems.Add(mySqlReader("CTNMAINID"))


                    .SubItems.Add(mySqlReader("CTNSHIPNAME"))
                    .SubItems.Add(mySqlReader("CTNBOOKING"))
                    .SubItems.Add(mySqlReader("CTNYARD"))


                End With


             


            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

    End Sub

    Private Sub ListView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.Click

        idcontainer = ListView1.SelectedItems(0).SubItems(4).Text
        txt_container.Text = ListView1.SelectedItems(0).SubItems(0).Text
        TextBox1.Text = ListView1.SelectedItems(0).SubItems(2).Text
        agentline = ListView1.SelectedItems(0).SubItems(1).Text
        If Trim(ListView1.SelectedItems(0).SubItems(2).Text) = "40'HC" Then
            TextBox4.Text = "45G1"
        ElseIf Trim(ListView1.SelectedItems(0).SubItems(2).Text) = "20'GP" Then
            TextBox4.Text = "22G1"
        End If

    End Sub
    Private Sub ListView2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView2.Click

            txt_booking_no.Text = ""
            txt_customer.Text = ""
            txt_booking_no.Text = ListView2.SelectedItems(0).SubItems(0).Text
            txt_customer.Text = ListView2.SelectedItems(0).SubItems(1).Text
            idbooking = ListView2.SelectedItems(0).SubItems(4).Text


    End Sub

    Private Sub ButtonX3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX3.Click





        Dim intx As Integer = 0


        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select count(idborrow) as borrow from borrow where bookid = '" & idbooking & "' and ctnid ='" & idcontainer & "' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())
                intx = mySqlReader("borrow")
            End While

        Catch ex As Exception

        End Try






        If intx = 0 Then


            If idbooking <> "" And idcontainer <> "" Then

                mysql.Close()

                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Dim commandText3 As String
                commandText3 = "UPDATE ctnmain SET CTNSTRING  = '" & txt_container.Text & "',CTNSTAT = '1',CTNSEALID = '" & txt_sealid.Text & "',CTNWEIGHT ='" & TextBox3.Text & "',CTNVOYS = '" & frmsearch_voyage1.idvoyage & "',CTNINS ='" & txt_insurance.Text & "' where CTNMAINID = '" & idcontainer & "'; "


                Try
                    mySqlCommand.CommandText = commandText3
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = mysql

                    mySqlCommand.ExecuteNonQuery()

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()



                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Try
                    mySqlCommand.Parameters.Clear()
                    mySqlCommand.CommandText = "insert into borrow ( CTNID, BOOKID, COMNAME, CARID,TIMEHHMM,TIMEDATE,INSURANCE,TIMEDATEIN,TIMEHHMMIN,BCTNTYPETHAI,BCTNTYPEPAE,BRCHECK,BRCHECK1) values (@CTNID,@BOOKID,@COMNAME,@CARID,@TIMEHHMM,@TIMEDATE,@INSURANCE,@TIMEDATEIN,@TIMEHHMMIN,@BCTNTYPETHAI,@BCTNTYPEPAE,@CHECK,@CHECK1)"
                    mySqlCommand.Connection = mysql
                    mySqlCommand.Parameters.AddWithValue("@CTNID", idcontainer)
                    mySqlCommand.Parameters.AddWithValue("@BOOKID", idbooking)
                    mySqlCommand.Parameters.AddWithValue("@COMNAME", txt_name_car.Text)
                    mySqlCommand.Parameters.AddWithValue("@CARID", txt_no_car.Text)

                    mySqlCommand.Parameters.AddWithValue("@TIMEHHMM", DateTimePicker4.Text)
                    mySqlCommand.Parameters.AddWithValue("@TIMEDATE", DateTimePicker2.Value.Date.ToString("dd-MM-yyyy"))

                    mySqlCommand.Parameters.AddWithValue("@INSURANCE", txt_insurance.Text)
                    mySqlCommand.Parameters.AddWithValue("@TIMEHHMMIN", DateTimePicker1.Text)
                    mySqlCommand.Parameters.AddWithValue("@TIMEDATEIN", DateTimePicker3.Value.Date.ToString("dd-MM-yyyy"))

                    mySqlCommand.Parameters.AddWithValue("@BCTNTYPETHAI", TextBox1.Text)
                    mySqlCommand.Parameters.AddWithValue("@BCTNTYPEPAE", TextBox4.Text)
                    mySqlCommand.Parameters.AddWithValue("@CHECK", "0")
                    mySqlCommand.Parameters.AddWithValue("@CHECK1", "0")


                    mySqlCommand.ExecuteNonQuery()
                    mysql.Close()
                    clearcode()
                    txtsearch.Text = ""
                    functionCal()
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try

            Else
                MsgBox("กรุณาตรวจสอบ Booking หรือ Container")
            End If

        Else
            MsgBox("ตู้นี้ถูกใช้งานแล้ว")
        End If
    End Sub


    Public Sub clearcode()
        idcontainer = ""
        agentline = ""
        idbooking = ""
        DateTimePicker4.Text = ""
        DateTimePicker1.Text = ""
        txt_booking_no.Text = ""
        txt_container.Text = ""
        txt_customer.Text = ""
        txt_sealid.Text = ""
        TextBox3.Text = ""
        txt_insurance.Text = ""
    End Sub
    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        Dim cf As New frmview_voyage

        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()

    End Sub
    Public Sub functionCal()

        mysql.Close()
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select * from  ctnmain   where  CTNSTAT = '1'    and ( CTNSTRING like '%" & txtsearch.Text & "%' or CTNVOYN like '%" & txtsearch.Text & "%' or CTNAGENT like '%" & txtsearch.Text & "%');"        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())

                With ListView1.Items.Add(mySqlReader("CTNSTRING"))


                    If mySqlReader("CTNAGENT") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("CTNAGENT"))
                    End If
                    If mySqlReader("CTNSIZE") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("CTNSIZE"))
                    End If


                    .SubItems.Add("N" + Format(mySqlReader("CTNVOYN"), "000"))
                    .SubItems.Add(mySqlReader("CTNMAINID"))
                    .SubItems.Add(mySqlReader("CTNSHIPNAME"))
                    .SubItems.Add(mySqlReader("CTNBOOKING"))
                    .SubItems.Add(mySqlReader("CTNYARD"))

                End With


            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()




    End Sub

    Private Sub ButtonX2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX2.Click
        functionCal()
    End Sub

    Private Sub txtsearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtsearch.KeyDown
        If e.KeyCode = Keys.Enter Then
            functionCal()
        End If
    End Sub

    Private Sub ListView1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView1.SelectedIndexChanged

    End Sub
End Class